﻿Imports System.Drawing.Printing
Imports System.Data.SqlClient
Imports RTF
Imports Microsoft.Office.Interop

Module modWYSIWYG
    Private Const WM_USER As Long = &H400
    Private Const EM_FORMATRANGE As Long = WM_USER + 57
    Private Const EM_SETTARGETDEVICE As Long = WM_USER + 72
    Public Declare Function CreateDC Lib "gdi32" Alias "CreateDCA" (ByVal lpDriverName As String, ByVal lpDeviceName As String, _
    ByVal lpOutput As Integer, ByVal lpInitData As Integer) As Integer
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Integer, ByVal wMsg As Integer, _
    ByVal wParam As Integer, ByVal lParam As Integer) As Integer
    Public Function WYSIWYG_RTF(ByRef RTF As RichTextBox, ByRef pd As PrintDocument)
        Dim LineWidth As Integer
        Dim PrinterhDC As Integer
        Dim r As Integer
        PrinterhDC = CreateDC(pd.DefaultPageSettings.PrinterSettings.GetHdevnames.ToString, pd.DefaultPageSettings.PrinterSettings.PrinterName, 0, 0)
        If pd.DefaultPageSettings.Landscape Then
            '1440 Twips=1 Inch
            LineWidth = (pd.DefaultPageSettings.PaperSize.Height - (pd.DefaultPageSettings.Margins.Right + pd.DefaultPageSettings.Margins.Left)) * 14.4
        Else
            LineWidth = (pd.DefaultPageSettings.PaperSize.Width - (pd.DefaultPageSettings.Margins.Right + pd.DefaultPageSettings.Margins.Left)) * 14.4
        End If
        r = SendMessage(RTF.Handle, EM_SETTARGETDEVICE, PrinterhDC, LineWidth)
        WYSIWYG_RTF = LineWidth
    End Function
End Module

Public Class ReportDesignerForm



    Private LineNumbers As New List(Of Integer)

    Private m_nFirstCharOnPage As Integer

    Private FieldCodes As New List(Of String)


    Public AllFieldsView As DataView
    Dim DataTableFields As DataView

    Dim ChangesMade As Boolean
    Dim ReportID As String
    Public MainReportView As DataView
    Public TempBox As String
    Public ReportHTML As String = ""
    Public TheReportName As String
    Dim AllocationIDs As New List(Of String)
    Dim PageNumber As Integer
    Public LineWidth As Integer = 0

    Dim ProcessCancelled As Boolean = False

    Private Sub UpdateToolBarBoxes()
        SetFontSizeBox()
        SetFontBox()

    End Sub

    Private Sub PrintDocument1_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles PrintDocument1.BeginPrint
        ' Start at the beginning of the text
        m_nFirstCharOnPage = 0
        PageNumber = 1

    End Sub

    Private Sub AddAllocationID(ByVal ID As String)

        'check if it exists
        Dim r As Integer
        Dim exists As Boolean = False

        If AllocationIDs.Count = 0 Then
            AllocationIDs.Add("'" & ID & "'")
        Else
            For r = 0 To AllocationIDs.Count - 1
                If AllocationIDs(r) = "'" & ID & "'" Then
                    exists = True
                End If
            Next

            If exists <> True Then
                AllocationIDs.Add("'" & ID & "'")
            End If

        End If

    End Sub

    Private Sub PrintDocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        ' To print the boundaries of the current page margins
        ' uncomment the next line:
        'e.Graphics.DrawRectangle(System.Drawing.Pens.Blue, e.MarginBounds)

        ' make the RichTextBoxEx calculate and render as much text as will
        ' fit on the page and remember the last character printed for the
        ' beginning of the next page


        Dim newPoint As Point = Point.Empty

        newPoint.X = e.MarginBounds.Left
        newPoint.Y = e.MarginBounds.Bottom

        Dim TheString As String

        TheString = TheReportName & " printed on " & Now().ToString

        e.Graphics.DrawString(TheString, System.Drawing.SystemFonts.DefaultFont, Brushes.Black, newPoint)

        m_nFirstCharOnPage = RichTextBoxEx1.FormatRange(False, _
                                                e, _
                                                m_nFirstCharOnPage, _
                                                RichTextBoxEx1.TextLength)

        ' check if there are more pages to print
        If (m_nFirstCharOnPage < RichTextBoxEx1.TextLength) Then
            e.HasMorePages = True
        Else
            e.HasMorePages = False
        End If

        PageNumber = PageNumber + 1
        ' PrintPreviewForm.NumberofPages = PrintPreviewForm.NumberofPages + 1
        'PrintPreviewForm.PageLabel.Text = "Page 1 of " & PrintPreviewForm.NumberofPages

    End Sub

    Private Sub PrintDocument1_EndPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles PrintDocument1.EndPrint
        ' Clean up cached information
        RichTextBoxEx1.FormatRangeDone()
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

        PageSetupDialog1.ShowDialog()
        LineWidth = WYSIWYG_RTF(Me.RichTextBoxEx1, Me.PrintDocument1)
        Me.RichTextBoxEx1.Refresh()
        '  SplitContainer1.Panel2.Width = LineWidth / 14.4
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        NewReport()

    End Sub

    Private Sub NewReport()
        Job("Preparing the Report Designer...")

        Toolpanel.Height = FileToolStrip.Height + EditToolStrip.Height

        LoadFontBox()

        PrintDocument1.DefaultPageSettings.Landscape = False
        PrintDocument1.DefaultPageSettings.Margins.Left = 39
        PrintDocument1.DefaultPageSettings.Margins.Right = 39
        PrintDocument1.DefaultPageSettings.Margins.Top = 39
        PrintDocument1.DefaultPageSettings.Margins.Bottom = 39

        LineWidth = WYSIWYG_RTF(Me.RichTextBoxEx1, Me.PrintDocument1)
        RichTextBoxEx1.Rtf = ""
        Me.RichTextBoxEx1.Refresh()
        UpdateToolBarBoxes()

        '  SplitContainer1.Panel2.Width = LineWidth / 14.4
        '  RichTextBoxEx1.ScrollBars = RichTextBoxScrollBars.Both
        'g = Me.RichTextBoxEx1.CreateGraphics
        'LineNumbers.Add(1)


        'load the treeview
        FieldsView.Nodes.Clear()
        LoadProviderfields()
        LoadProducts()
        LoadStages()
        LoadFactors()

        CreateFieldsView()
        CreateDataTableFieldsView()
        ChangesMade = False
        ReportID = ""
        RemovePubBut.Visible = False
        Me.Text = "New report"

        Job("Ready")
    End Sub


    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        If PrintDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            PrintDocument1.PrinterSettings.PrinterName = PrintDialog1.PrinterSettings.PrinterName
            PrintDocument1.PrinterSettings.Copies = PrintDialog1.PrinterSettings.Copies
            PrintDocument1.Print()
        End If


    End Sub

    Private Sub ReplaceValues(ByVal TheRID As String, ByVal Period_ID As Integer)


        Dim sql As String


        sql = ReportSQL(TheRID, Period_ID)

        MainReportView = LoadDataView(sql)
        '   Clipboard.Clear()
        '  Clipboard.SetText(sql)

        If MainReportView.Count <> 0 Then

            LoadRecord(0)

        End If



    End Sub

    Public Sub LoadRecord(ByVal RecordNumber As Integer)
        'only call this query once the view has been loaded
        Dim DP As Integer

        If RecordNumber > MainReportView.Count - 1 Then Exit Sub

        RichTextBoxEx1.Rtf = TempBox

        For Each r In AllFieldsView
            While RichTextBoxEx1.Find("[" & r.Item("Field_Code") & "]") <> -1
                DP = GetDecimals(r.Item("Field_Code"))
                If DP = -1 Then
                    RichTextBoxEx1.SelectedText = MainReportView(RecordNumber).Item(r.Item("Field_Code")).ToString
                Else
                    If IsDBNull(MainReportView(RecordNumber).Item(r.Item("Field_Code"))) Then
                        RichTextBoxEx1.SelectedText = "0"
                    Else
                        RichTextBoxEx1.SelectedText = FormatNumber(MainReportView(RecordNumber).Item(r.Item("Field_Code")), DP, TriState.True, TriState.False, TriState.True)
                    End If

                End If
            End While
        Next
    End Sub

    Private Function GetDecimals(ByVal Field_Code As String) As Integer
        Dim r As DataRowView
        Dim TheVal As Integer = -1

        For Each r In AllFieldsView
            If r.Item("Field_Code") = Field_Code Then
                TheVal = r.Item("Decimals")
            End If
        Next

        GetDecimals = TheVal
    End Function

    Private Function GetZeroText(ByVal Field_Code As String) As String
        Dim r As DataRowView
        Dim TheVal As String = "0"

        For Each r In AllFieldsView
            If r.Item("Field_Code") = Field_Code Then
                TheVal = r.Item("Zero_Text")
            End If
        Next

        GetZeroText = TheVal
    End Function

    Private Sub FontSizeBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FontSizeBox.SelectedIndexChanged
        RichTextBoxEx1.SetSelectionSize(FontSizeBox.Text)
        RichTextBoxEx1.Focus()

    End Sub

    Private Sub SetFontSizeBox()

        If IsNothing(RichTextBoxEx1.SelectionFont) Then
            FontSizeBox.Text = ""
            Exit Sub
        End If

        If RichTextBoxEx1.SelectionFont.Size = 13 Then
            FontSizeBox.Text = ""
        Else
            FontSizeBox.Text = RichTextBoxEx1.SelectionFont.Size
        End If


    End Sub

    Private Sub SetFontBox()
        If IsNothing(RichTextBoxEx1.SelectionFont) Then
            FontBox.Text = ""
        Else
            FontBox.Text = RichTextBoxEx1.SelectionFont.Name
        End If


    End Sub

    Private Sub RichTextBoxEx1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBoxEx1.Click
        UpdateToolBarBoxes()


    End Sub

    Private Sub RichTextBoxEx1_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles RichTextBoxEx1.KeyPress
        UpdateToolBarBoxes()


    End Sub

    Private Sub LoadFontBox()
        Dim colFonts As Drawing.Text.InstalledFontCollection = New Drawing.Text.InstalledFontCollection
        Dim fontFamilies() As FontFamily = colFonts.Families
        Dim i As Integer

        FontBox.Items.Clear()

        For i = 0 To fontFamilies.Length - 1 Step 1
            Me.FontBox.Items.Add(fontFamilies(i).Name)
        Next
    End Sub

    Private Sub FontBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FontBox.SelectedIndexChanged
        RichTextBoxEx1.SetSelectionFont(FontBox.Text)
        RichTextBoxEx1.Focus()

    End Sub

    Private Sub AddImage(ByVal filename As String)
        Try
            Dim img As Image = Image.FromFile(filename)
            Dim orgData = Clipboard.GetDataObject
            Clipboard.SetImage(img)
            Me.RichTextBoxEx1.Paste()

            Clipboard.SetDataObject(orgData)
        Catch ex As Exception
            MessageBox.Show("Could not add the image.  Make sure it is a valid image file.", "No Image", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Private Sub ToolStripButton5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton5.Click
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            AddImage(OpenFileDialog1.FileName)
        End If
    End Sub



    Public Function RTF_BuildTable(ByVal TheTableView As DataView) As String
        Dim RTF As String
        Dim c As Integer

        ' Load NumCells variable to write table 
        ' row properties
        Dim NumCells As Integer
        NumCells = TheTableView.Table.Columns.Count

        ' load NumRows variable to set up table 
        ' contents loop for recordset
        Dim NumRows As Integer
        NumRows = TheTableView.Table.Rows.Count

        ' write table contents
        RTF = "{\rtf1\ansi"
        'RTF = "{"
        For k = 1 To NumRows
            RTF = RTF & "\trowd\trautofit1"

            For i = 1 To NumCells
                RTF = RTF & "\clbrdrt\brdrs\clbrdrl\brdrs\clbrdrb\brdrs\clbrdrr\brdrs\cellx" & i & "000"
            Next

            For c = 1 To NumCells
                RTF = RTF & TheTableView(k - 1).Item(c - 1) & "\intbl\cell "
            Next

            RTF = RTF & "\row\pard\par "

        Next

        RTF = RTF & "}"
        RTF_BuildTable = RTF
    End Function

    Private Sub ToolStripButton7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton7.Click
        FontDialog1.Font = RichTextBoxEx1.SelectionFont
        If FontDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            With RichTextBoxEx1
                .SetSelectionFont(FontDialog1.Font.Name)
                .SetSelectionBold(FontDialog1.Font.Bold)
                .SetSelectionItalic(FontDialog1.Font.Italic)
                .SetSelectionSize(FontDialog1.Font.Size)
                .SetSelectionUnderlined(FontDialog1.Font.Underline)
                UpdateToolBarBoxes()
            End With

        End If
    End Sub

    Private Sub LeftToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LeftToolStripMenuItem.Click
        RichTextBoxEx1.SelectionAlignment = HorizontalAlignment.Left

    End Sub

    Private Sub CenterToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CenterToolStripMenuItem.Click
        RichTextBoxEx1.SelectionAlignment = HorizontalAlignment.Center

    End Sub

    Private Sub RightToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RightToolStripMenuItem.Click
        RichTextBoxEx1.SelectionAlignment = HorizontalAlignment.Right

    End Sub

    Private Sub ToolStripButton4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton4.Click
        Me.Close()

    End Sub

    Private Sub LoadProducts()
        Dim TheView As DataView
        Dim Folders As DataView
        Dim sql As String
        Dim r As DataRowView
        Dim rr As DataRowView

        sql = "select * from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Folder_ID is null order by folder_ID, Product_description"

        TheView = LoadDataView(sql)
        Dim FolderNode As TreeNode
        Dim ProductNode As TreeNode

        With FieldsView

            ProductNode = .Nodes.Add("Modelling_Steps_Products", "Products", 2, 2)

            For Each r In TheView
                ProductNode.Nodes.Add(r.Item("Product_Number"), r.Item("Product_Description"), 0, 0)
            Next
        End With

        sql = "select * from Modelling_Product_Folders where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' order by Folder_Description"

        Folders = LoadDataView(sql)

        For Each r In Folders
            FolderNode = ProductNode.Nodes.Add(r.Item("Folder_ID"), r.Item("Folder_Description"), 8, 8)
            sql = "select * from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Folder_ID = '" & r.Item("Folder_ID") & "' order by folder_ID, Product_description"

            TheView = LoadDataView(sql)

            For Each rr In TheView
                FolderNode.Nodes.Add(rr.Item("Product_Number"), rr.Item("Product_Description"), 0, 0)
            Next
        Next


    End Sub

    Private Sub LoadProviderfields()


        Dim ProviderNode As TreeNode

        With FieldsView

            ProviderNode = .Nodes.Add("All_Provider_Fields", "Provider Fields", 1, 1)

            ProviderNode.Nodes.Add("UPIN", "UPIN", 0, 0)
            ProviderNode.Nodes.Add("Provider_Name", "Provider Name", 0, 0)
            ProviderNode.Nodes.Add("Provider_Type_Code", "Provider Type Code", 0, 0)
            ProviderNode.Nodes.Add("Provider_Type_Description", "Provider Type", 0, 0)
            ProviderNode.Nodes.Add("LA_Code", "Local Authority Code", 0, 0)
            ProviderNode.Nodes.Add("LA_Name", "Local Authority Name", 0, 0)
            ProviderNode.Nodes.Add("Region_Code", "Region Code", 0, 0)
            ProviderNode.Nodes.Add("Region_Name", "Region Name", 0, 0)
            ProviderNode.Nodes.Add("Territory_Name", "Territory Name", 0, 0)
            ProviderNode.Nodes.Add("Territory_Code", "Terriotry Code", 0, 0)

        End With
    End Sub

    Private Sub LoadStages()
        Dim StageNode As TreeNode
        Dim AlNode As TreeNode
        Dim sql As String
        Dim AlView As DataView
        Dim SView As DataView
        Dim LView As DataView
        Dim HasLearners As Boolean
        Dim HasLearnersMain As Boolean

        With FieldsView
            StageNode = .Nodes.Add("Stages", "Stages", 4, 4)

            sql = "select Distinct ms.Allocation_ID, al.Allocation_Description from dbo.Modelling_Stages ms, Allocation_Lines al " & _
                  "where MS.Allocation_ID = al.Allocation_ID  " & _
                  "and ms.scenario_id = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "'"

            AlView = LoadDataView(sql)

            Dim ar As DataRowView
            HasLearnersMain = False

            For Each ar In AlView
                HasLearners = False
                AlNode = StageNode.Nodes.Add(ar.Item("Allocation_ID"), ar.Item("Allocation_Description"), 5, 5)

                sql = "select * from modelling_stages where allocation_id = '" & ar.Item("Allocation_ID") & "' and scenario_id = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "'"

                SView = LoadDataView(sql)

                Dim sr As DataRowView

                For Each sr In SView

                    AlNode.Nodes.Add(sr.Item("Stage_Number"), sr.Item("Stage_Description"), 0, 0)

                    'check if this stage has learners
                    sql = "select * from modelling_steps where allocation_id = '" & ar.Item("Allocation_ID") & "' and scenario_id = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Stage_Number = " & sr.Item("Stage_Number") & _
                        " and Learner_Flag = 1"

                    LView = LoadDataView(sql)
                    If LView.Count <> 0 Then
                        HasLearners = True
                        HasLearnersMain = True
                        'also add this line as a stage learner
                        AlNode.Nodes.Add("Learners" & sr.Item("Stage_Number"), sr.Item("Stage_Description") & " Learners", 0, 0)
                    End If
                Next

                'add A Total Node
                AlNode.Nodes.Add("Allocation Funding", ar.Item("Allocation_Description") & " Funding", 0, 0)

                If HasLearners Then
                    AlNode.Nodes.Add("Allocation Learners", ar.Item("Allocation_Description") & " Learners", 0, 0)
                End If
            Next

            StageNode.Nodes.Add("Total Funding", "Total Funding", 0, 0)
            If HasLearnersMain Then
                StageNode.Nodes.Add("Total Learners", "Total Learners", 0, 0)
            End If
        End With

        AlView = Nothing
        SView = Nothing
        LView = Nothing

    End Sub

    Private Sub LoadFactors()

        Dim sql As String
        Dim TheView As DataView
        Dim FactorsNode As TreeNode
        Dim DataCutNode As TreeNode
        Dim r As DataRowView

        sql = "select distinct datacut_id, Description from modelling_factors where status = 'Approved'"
        TheView = LoadDataView(sql)

        With FieldsView
            FactorsNode = .Nodes.Add("Modelling_Factors", "Factors", 6, 6)
            For Each r In TheView
                DataCutNode = FactorsNode.Nodes.Add(r.Item("DataCut_ID"), r.Item("Description"), 7, 7)

                DataCutNode.Nodes.Add("Programme_Weighting", "Programme Weighting", 0, 0)
                DataCutNode.Nodes.Add("Area_Cost", "Area Cost", 0, 0)
                DataCutNode.Nodes.Add("Success_Factor", "Success Factor", 0, 0)
                DataCutNode.Nodes.Add("Disadvantage_Factor", "Disadvantage Factor", 0, 0)
                DataCutNode.Nodes.Add("Adjustment_Factor", "Adjustment Factor", 0, 0)
                DataCutNode.Nodes.Add("Short_Course_Modifier", "Short Course Modifier", 0, 0)
                DataCutNode.Nodes.Add("Long_Term_Residential_Factor", "Long Term Residential Factor", 0, 0)
                DataCutNode.Nodes.Add("Care_Standards_Factor", "Care Standards Factor", 0, 0)
                DataCutNode.Nodes.Add("Value", "Provider Factor", 0, 0)
            Next
        End With



    End Sub

    Private Sub FieldsView_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FieldsView.DoubleClick
        Dim CodeExists As Boolean = False

        Try
            If IsNothing(FieldsView.SelectedNode.Parent) Then
                Exit Sub
            End If
        Catch ex As Exception
            Exit Sub
        End Try


        'does the selected node have children?
        If FieldsView.SelectedNode.Nodes.Count > 0 Then Exit Sub

        Dim TheCode As String

        If Mid(FieldsView.SelectedNode.Text, 1, 2) = "NF" Then Exit Sub


        If FieldsView.SelectedNode.Text.Contains("[") Then
            TheCode = Mid(FieldsView.SelectedNode.Text, 2, 3)
            CodeExists = True
        Else
            TheCode = NextFieldCode()
            FieldsView.SelectedNode.Text = "[" & TheCode & "]" & " " & FieldsView.SelectedNode.Text
            CodeExists = False
        End If
        FieldsView.SelectedNode.SelectedImageIndex = 3
        FieldsView.SelectedNode.ImageIndex = 3

        RichTextBoxEx1.SelectedText = "[" & TheCode & "]"


        If Not CodeExists Then
            'add the field to the view, find out what table name it belongs to
            Dim Table_Name As String = ""
            Dim Field_Name As String = ""
            Dim TheValue As String = ""
            Dim Parent As String = ""
            Dim Parent_Parent As String = ""
            Dim Decimals As Integer = 0
            Dim Node As String = ""


            With FieldsView
                'Providers
                If .SelectedNode.Parent.Name = "All_Provider_Fields" Then
                    Table_Name = "All_Provider_Fields"
                    Field_Name = .SelectedNode.Name
                    Parent = .SelectedNode.Parent.Name
                    Decimals = -1
                    Node = .SelectedNode.Name
                Else
                    'Products
                    If .SelectedNode.Parent.Name = "Modelling_Steps_Products" Then
                        Table_Name = "Modelling_Steps_Products"
                        Field_Name = "Product_Number"
                        TheValue = .SelectedNode.Name
                        Parent = .SelectedNode.Parent.Name
                        Decimals = 2
                        Node = .SelectedNode.Name
                    Else
                        'Scenario Total
                        If .SelectedNode.Parent.Name = "Stages" Then
                            Table_Name = "Totals"
                            Field_Name = .SelectedNode.Name
                            Parent = .SelectedNode.Parent.Name
                            Decimals = 0
                            Node = .SelectedNode.Name
                        Else
                            'Products (Folders)
                            If .SelectedNode.Parent.Parent.Name = "Modelling_Steps_Products" Then
                                Table_Name = "Modelling_Steps_Products"
                                Field_Name = "Product_Number"
                                TheValue = .SelectedNode.Name
                                Parent = .SelectedNode.Parent.Name
                                Decimals = 2
                                Node = .SelectedNode.Name
                            Else
                                'Factors
                                If .SelectedNode.Parent.Parent.Name = "Modelling_Factors" Then
                                    Table_Name = "Modelling_Factors"
                                    Field_Name = .SelectedNode.Name
                                    TheValue = .SelectedNode.Parent.Name
                                    Parent = .SelectedNode.Parent.Name
                                    Parent_Parent = .SelectedNode.Parent.Parent.Name
                                    Decimals = 3
                                    Node = .SelectedNode.Name
                                Else
                                    'Stages
                                    If .SelectedNode.Parent.Parent.Name = "Stages" Then
                                        Table_Name = "Modelling_Steps"
                                        Field_Name = .SelectedNode.Name
                                        TheValue = .SelectedNode.Parent.Name
                                        Parent = .SelectedNode.Parent.Name
                                        Parent_Parent = .SelectedNode.Parent.Parent.Name
                                        Decimals = 0
                                        Node = .SelectedNode.Name
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End With

            AddFields(Table_Name, Field_Name, TheCode, TheValue, Parent, Parent_Parent, Node, Decimals, "0")
        End If

        RichTextBoxEx1.Focus()

    End Sub

    Private Function NextFieldCode() As String
        Dim r As Integer
        Dim TheVal As String
        Dim TheChar

        If FieldCodes.Count = 0 Then
            FieldCodes.Add("A01")
            NextFieldCode = "A01"
        Else
            TheChar = "0"
            'first find the highest letter
            For r = 0 To FieldCodes.Count - 1
                If Asc(TheChar) < Asc(Mid(FieldCodes(r), 1, 1)) Then
                    TheChar = Mid(FieldCodes(r), 1, 1)
                End If
            Next

            'now the highest number
            TheVal = "0"

            For r = 0 To FieldCodes.Count - 1
                If Mid(FieldCodes(r), 1, 1) = TheChar Then
                    If TheVal < Mid(FieldCodes(r), 2, 2) Then
                        TheVal = Mid(FieldCodes(r), 2, 2)
                    End If

                End If
                
            Next

            'if the number is 99 then make the next number 01 and increase letter, else increase number only
            If TheVal = 99 Then
                TheVal = "01"
                TheChar = Chr(Asc(TheChar) + 1)
            Else
                TheVal = TheVal + 1
                If Len(TheVal) = 1 Then
                    TheVal = "0" & TheVal
                End If
            End If

            FieldCodes.Add(TheChar & TheVal)
            NextFieldCode = TheChar & TheVal
        End If
    End Function

    Private Sub CreateFieldsView()
        Dim sql As String

        'this will create a table view with no records
        sql = "select '' as Table_Name, '' as Field_Name, '' as Field_Code, '' as Value, '' as Parent, '' as Parent_Parent, '' as Node, 1 as Decimals, '0' as Zero_Text where 1 = 2"

        AllFieldsView = LoadDataView(sql)

    End Sub

    Private Sub CreateDataTableFieldsView()
        Dim sql As String

        'this will create a table view with no records
        sql = "SELECT [Report_ID],[Field_Code],[Order_Num],[Header] FROM [RD_Published_DataTable_Fields] where 1 = 2"

        DataTableFields = LoadDataView(sql)

    End Sub

    Private Sub AddFields(ByVal Table_Name As String, ByVal Field_Name As String, ByVal Field_Code As String, ByVal Value As String, ByVal Parent As String, ByVal Parent_Parent As String, ByVal Node As String, ByVal Decimals As Integer, ByVal Zero_Text As String)

        Dim rowView As DataRowView = AllFieldsView.AddNew

        ' Change values in the DataRow.
        rowView("Table_Name") = Table_Name
        rowView("Field_Name") = Field_Name
        rowView("Field_Code") = Field_Code
        rowView("Value") = Value
        rowView("Parent") = Parent
        rowView("Parent_Parent") = Parent_Parent
        rowView("Decimals") = Decimals
        rowView("Node") = Node
        rowView("Zero_Text") = Zero_Text
        rowView.EndEdit()

    End Sub

    Private Sub EditDecimals(ByVal Field_COde As String, ByVal Decimals As Integer)
        AllFieldsView.Sort = "Field_Code"

        Dim rowview As DataRowView = AllFieldsView.Item(AllFieldsView.Find(Field_COde))

        rowview.BeginEdit()
        rowview.Item("Decimals") = Decimals
        rowview.EndEdit()

    End Sub

    Private Function FindDecimals(ByVal Field_COde As String) As Integer
        AllFieldsView.Sort = "Field_Code"

        Dim rowview As DataRowView = AllFieldsView.Item(AllFieldsView.Find(Field_COde))


        FindDecimals = rowview.Item("Decimals")


    End Function


    Private Sub EditZeroText(ByVal Field_COde As String, ByVal TheText As String)
        AllFieldsView.Sort = "Field_Code"

        Dim rowview As DataRowView = AllFieldsView.Item(AllFieldsView.Find(Field_COde))

        rowview.BeginEdit()
        rowview.Item("Zero_Text") = TheText
        rowview.EndEdit()

    End Sub

    Private Function FindZeroText(ByVal Field_COde As String) As String
        AllFieldsView.Sort = "Field_Code"

        Dim rowview As DataRowView = AllFieldsView.Item(AllFieldsView.Find(Field_COde))


        FindZeroText = rowview.Item("Zero_Text")


    End Function

    Private Function LevelSQL(ByVal Scenario_ID As String, ByVal ScenarioTotal As Boolean, ByVal Allocation_ID As String, ByVal Stage_Number As Integer, ByVal StageOrProduct As String, ByVal Field_Code As String, ByVal FundingOrLearners As String, ByVal TheRid As String, ByVal Session_ID As String, ByVal Period_ID As Integer) As String
        Dim sql As String
        Dim isql As String
        Dim zReportID As String
        Dim Budget_ID As String = ""
        Dim TheVal As String
        Dim TheView As DataView

        If ReportID = "" Then
            zReportID = "Temp"
        Else
            zReportID = ReportID
        End If

        'delete all fields from the table
        sql = "delete from RD_Data where Report_ID = '" & zReportID & "' and User_Name = '" & CurrentUser.Name & "' and Field_Code = '" & Field_Code & "'"
        RunNonQuery(sql)

        If ScenarioTotal = False Then
            If StageOrProduct <> "" Then
                If StageOrProduct.ToUpper = "STAGE" Then

                    ' sql = Modelling_ResultsForm.StageSQL(Scenario_ID, Allocation_ID, Stage_Number, "Stage", False)


                    With Modelling_Process
                        .Text = Me.Text


                        sql = "select * from dbo.Modelling_Stages where scenario_id = '" & Scenario_ID & "'" & _
                            " and Allocation_ID = '" & Allocation_ID & "'" & _
                            " and Stage_Number = " & Stage_Number & _
                            " order by Allocation_ID, Stage_Number"

                        .StagesSQL = sql
                        .IsStage = True
                        .IsAllocationLine = False
                        .TheRID = TheRid
                        .CanClearTempData = False
                        .ReturnSQL = True
                        .Session_ID_Main = Session_ID
                        .Process_Period_ID = Period_ID
                        .IsReportDesigner = True
                        If .ShowDialog = Windows.Forms.DialogResult.OK Then
                            sql = .SQLTextBox.Text
                        Else
                            ProcessCancelled = True
                            Return ""
                            Exit Function
                        End If
                    End With


                Else
                    'sql = Modelling_ResultsForm.StageSQL(Scenario_ID, "", Stage_Number, "Product", False)

                    With Modelling_Process
                        .Text = Me.Text


                        sql = "select * from dbo.Modelling_Products where scenario_id = '" & Scenario_ID & "'" & _
                           " and Product_Number = " & Stage_Number & _
                           " order by Product_Number"

                        .StagesSQL = sql
                        .IsStage = False
                        .IsAllocationLine = False
                        .TheRID = TheRid
                        .CanClearTempData = False
                        .ReturnSQL = True
                        .Session_ID_Main = Session_ID
                        .Process_Period_ID = Period_ID
                        .IsReportDesigner = True
                        If .ShowDialog = Windows.Forms.DialogResult.OK Then
                            sql = .SQLTextBox.Text
                        Else
                            ProcessCancelled = True
                            Return ""
                            Exit Function
                        End If
                    End With


                End If
            Else
                'get budget ID
                sql = "select * from Allocation_Lines where Allocation_ID = '" & Allocation_ID & "'"
                TheView = LoadDataView(sql)

                If TheView.Count <> 0 Then
                    Budget_ID = TheView(0).Item("Budget_ID").ToString
                Else
                    Budget_ID = ""
                End If

                sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
          " and s.Scenario_ID = '" & Scenario_ID & "' and s.Allocation_ID = '" & Allocation_ID & "'" & _
          " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number " & _
          " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

                'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 2, Budget_ID, False)

                With Modelling_Process
                    .Text = Me.Text

                    .StagesSQL = sql
                    .IsStage = True
                    .IsAllocationLine = True
                    .TheRID = TheRid
                    .CanClearTempData = False
                    .ReturnSQL = True
                    .Session_ID_Main = Session_ID
                    .Process_Period_ID = Period_ID
                    .IsReportDesigner = True
                    If .ShowDialog = Windows.Forms.DialogResult.OK Then
                        sql = .SQLTextBox.Text
                    Else
                        ProcessCancelled = True
                        Return ""
                        Exit Function
                    End If
                End With


            End If
        Else
            sql = "select s.*, st.Stage_Description, al.Budget_ID  from dbo.Modelling_Steps s, dbo.Allocation_Lines al, dbo.Modelling_Stages st where s.Allocation_ID = al.Allocation_ID " & _
            " and s.Scenario_ID = '" & Scenario_ID & "' " & _
            " and st.Scenario_ID = s.Scenario_ID and st.Allocation_ID = s.Allocation_ID and st.Stage_Number = s.Stage_Number and al.Hidden = 0 " & _
            " order by s.Scenario_ID, al.Budget_ID, s.Allocation_ID, s.Stage_Number, s.Step_Number"

            'sql = Modelling_ResultsForm.ScenarioSQLv3(sql, 4, "", False)


            With Modelling_Process
                .Text = Me.Text

                .StagesSQL = sql
                .IsStage = True
                .IsAllocationLine = True
                .TheRID = TheRid
                .CanClearTempData = False
                .ReturnSQL = True
                .Session_ID_Main = Session_ID
                .Process_Period_ID = Period_ID
                .IsReportDesigner = True
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    sql = .SQLTextBox.Text
                Else
                    ProcessCancelled = True
                    Return ""
                    Exit Function
                End If
            End With

        End If

        'now insert all the values
        If FundingOrLearners.ToUpper = "FUNDING" Then
            TheVal = "[Final Result]"
        Else
            TheVal = "Learners"
        End If

        If TheRid = "" Then
            isql = "insert into RD_Data Select '" & zReportID & "', '" & CurrentUser.Name & "', RID, '" & Field_Code & "', " & TheVal & " from (" & sql & ") a "
        Else
            isql = "insert into RD_Data Select '" & zReportID & "', '" & CurrentUser.Name & "', RID, '" & Field_Code & "', " & TheVal & " from (" & sql & ") a where RID = '" & TheRid & "'"
        End If

        RunNonQuery(isql)

        LevelSQL = "Select RID, Value as [Final Result] from RD_Data where Report_ID = '" & zReportID & "' and User_Name = '" & CurrentUser.Name & "' and Field_Code = '" & Field_Code & "'"

    End Function

    Private Function ReportSQL(ByVal TheRid As String, ByVal Period_ID As Integer) As String
        Dim sql As String = ""
        Dim SelectSQL As String = ""
        Dim FromSQL As String = ""
        Dim r As DataRowView
        Dim sr As DataRowView
        Dim i As Integer = -1
        Dim t As Integer = -1
        Dim FactorsList As New List(Of String)
        Dim FactorListNumber As New List(Of String)
        Dim FactorUsed As Boolean = False
        Dim Session_ID As String = ""

        Session_ID = Format(Now, "ddMMyyhhmmssff")

        FromSQL = "All_Provider_Fields APF"
        AllocationIDs.Clear()

        SelectSQL = "APF.Provider_Name, APF.LA_Name, APF.LA_Code, APF.Region_Code, APF.Territory_Name, APF.UPIN, APF.RID, APF.Provider_Type_Code"


        If AllFieldsView.Count <> 0 Then
            ProgressBarForm.TheProgressBar.Minimum = 0
            ProgressBarForm.TheProgressBar.Maximum = AllFieldsView.Count - 1
            ProgressBarForm.TheProgressBar.Step = 1
        End If

        ProgressBarForm.Show()

        For Each r In AllFieldsView
            ProgressBarForm.TheProgressBar.PerformStep()

            If ProcessCancelled Then
                Try
                    ProgressBarForm.Close()

                Catch ex As Exception

                End Try
                Return ""
                Exit Function
            End If

            If RichTextBoxEx1.Find("[" & r.Item("Field_Code") & "]") <> -1 Then
                'only do the sql statement if the field exists in the report
                i = i + 1

                'If r.Item("Field_Code") = "A17" Then
                '    MessageBox.Show("Stop")
                'End If

                'providers
                If r.Item("Table_Name") = "All_Provider_Fields" Then

                    If SelectSQL = "" Then
                        SelectSQL = "APF." & r.Item("Field_Name") & " as " & r.Item("Field_Code")
                    Else
                        SelectSQL = SelectSQL & ",APF." & r.Item("Field_Name") & " as " & r.Item("Field_Code")
                    End If


                End If

                'factors
                If r.Item("Table_Name") = "Modelling_Factors" Then
                    Dim FactNum As String
                    Dim FactorSQL As String

                    FactorSQL = "select * from Modelling_Factors where datacut_id = " & _
                                "(select DataCut_ID from dbo.Case_Templates where table_name_act = 'Modelling_Factors' and DataCut_ID_Act = '" & r.Item("Value") & "') " & _
                                "and Status = 'Approved' " & _
                                "union " & _
                                "select * from Modelling_Factors where datacut_ID = '" & r.Item("Value") & "' and Status = 'Approved' " & _
                                "and RID not in (select RID " & _
                                "from Modelling_Factors where datacut_id = " & _
                                "(select DataCut_ID from dbo.Case_Templates where table_name_act = 'Modelling_Factors' and DataCut_ID_Act = '" & r.Item("Value") & "') " & _
                                "and Status = 'Approved') "

                    'first see if this factor has already been added as a join, if not then join it
                    If FactorsList.Count = 0 Then
                        FromSQL = FromSQL & " left outer join (" & FactorSQL & ") Fact" & i & " on (APF.RID = Fact" & i & ".RID)"

                        FactorsList.Add(r.Item("Value"))
                        FactorListNumber.Add(i)
                        FactNum = i
                    Else
                        FactorUsed = False
                        For t = 0 To FactorsList.Count - 1
                            If FactorsList(t) = r.Item("Value") Then
                                FactorUsed = True
                                FactNum = FactorListNumber(t)
                            End If
                        Next

                        If FactorUsed = False Then
                            FromSQL = FromSQL & " left outer join (" & FactorSQL & ") Fact" & i & " on (APF.RID = Fact" & i & ".RID)"

                            FactorsList.Add(r.Item("Value"))
                            FactorListNumber.Add(i)
                            FactNum = i
                        End If
                    End If

                    If SelectSQL = "" Then
                        SelectSQL = "Fact" & FactNum & "." & r.Item("Field_Name") & " as " & r.Item("Field_Code")
                    Else
                        SelectSQL = SelectSQL & ",Fact" & FactNum & "." & r.Item("Field_Name") & " as " & r.Item("Field_Code")
                    End If

                End If

                'Products
                If r.Item("Table_Name") = "Modelling_Steps_Products" Then
                    Dim ProductSQL As String

                    'ProductSQL = Modelling_ResultsForm.StageSQL(ModelForm.ScenarioBox.SelectedValue.ToString, _
                    '                                       "", r.Item("Value"), "Product", False)

                    ProductSQL = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, False, "", r.Item("Value"), "Product", r.Item("Field_Code"), "Funding", TheRid, Session_ID, Period_ID)

                    FromSQL = FromSQL & " left outer join (" & ProductSQL & ") Product" & i & " on (APF.RID = Product" & i & ".RID)"

                    If SelectSQL = "" Then
                        SelectSQL = "Product" & i & ".[Final Result] as " & r.Item("Field_Code")
                    Else
                        SelectSQL = SelectSQL & ",Product" & i & ".[Final Result] as " & r.Item("Field_Code")
                    End If
                End If

                'Stages
                If r.Item("Table_Name") = "Modelling_Steps" Then
                    Dim StageSQL As String
                    StageSQL = ""
                    'not totals
                    If Not r.Item("Field_Name").ToString.Contains("Allocation") Then
                        If r.Item("Field_Name").ToString.Contains("Learners") Then
                            'Learners in the stage

                            AddAllocationID(r.Item("Value"))

                            'StageSQL = Modelling_ResultsForm.StageSQL(ModelForm.ScenarioBox.SelectedValue.ToString, _
                            '                                r.Item("Value"), Mid(r.Item("Field_Name"), 9, 10), "Stage", False)

                            StageSQL = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, False, r.Item("Value"), Mid(r.Item("Field_Name"), 9, 10), "Stage", r.Item("Field_Code"), "Learners", TheRid, Session_ID, Period_ID)
                            FromSQL = FromSQL & " left outer join (" & StageSQL & ") Stage" & i & " on (APF.RID = Stage" & i & ".RID)"

                            If SelectSQL = "" Then
                                SelectSQL = "Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                            Else
                                SelectSQL = SelectSQL & ",Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                            End If

                        Else
                            'Funding in the stage

                            AddAllocationID(r.Item("Value"))

                            'StageSQL = Modelling_ResultsForm.StageSQL(ModelForm.ScenarioBox.SelectedValue.ToString, _
                            '                                r.Item("Value"), r.Item("Field_Name"), "Stage", False)

                            StageSQL = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, False, r.Item("Value"), r.Item("Field_Name"), "Stage", r.Item("Field_Code"), "Funding", TheRid, Session_ID, Period_ID)
                            FromSQL = FromSQL & " left outer join (" & StageSQL & ") Stage" & i & " on (APF.RID = Stage" & i & ".RID)"

                            If SelectSQL = "" Then
                                SelectSQL = "Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                            Else
                                SelectSQL = SelectSQL & ",Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                            End If
                        End If

                    Else
                        'Allocation totals
                        'Dim StagesView As DataView
                        Dim StagesSelect As String = ""

                        'StagesView = LoadDataView("select * from modelling_stages where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Allocation_ID = '" & r.Item("Value") & "'")
                        AddAllocationID(r.Item("Value"))

                        If r.Item("Field_Name").ToString.Contains("Learners") Then
                            StagesSelect = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, False, r.Item("Value"), 0, "", r.Item("Field_Code"), "Learners", TheRid, Session_ID, Period_ID)
                        Else
                            StagesSelect = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, False, r.Item("Value"), 0, "", r.Item("Field_Code"), "Funding", TheRid, Session_ID, Period_ID)
                        End If

                        FromSQL = FromSQL & " left outer join (" & StagesSelect & ") Stage" & i & " on (APF.RID = Stage" & i & ".RID)"

                        If SelectSQL = "" Then
                            SelectSQL = "Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                        Else
                            SelectSQL = SelectSQL & ",Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                        End If

                    End If



                End If

                'Scenario Totals
                If r.Item("Table_Name") = "Totals" Then
                    Dim StagesSelect As String = ""

                    'StagesView = LoadDataView("select * from modelling_stages where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Allocation_ID = '" & r.Item("Value") & "'")
                    AddAllocationID(r.Item("Value"))

                    If r.Item("Field_Name").ToString.Contains("Learners") Then
                        StagesSelect = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, True, r.Item("Value"), 0, "", r.Item("Field_Code"), "Learners", TheRid, Session_ID, Period_ID)
                    Else
                        StagesSelect = LevelSQL(ModelForm.ScenarioBox.SelectedValue.ToString, True, r.Item("Value"), 0, "", r.Item("Field_Code"), "Funding", TheRid, Session_ID, Period_ID)
                    End If

                    FromSQL = FromSQL & " left outer join (" & StagesSelect & ") Stage" & i & " on (APF.RID = Stage" & i & ".RID)"

                    If SelectSQL = "" Then
                        SelectSQL = "Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                    Else
                        SelectSQL = SelectSQL & ",Stage" & i & ".[Final Result] as " & r.Item("Field_Code")
                    End If


                End If

            End If




        Next


        ProgressBarForm.Close()

        If SelectSQL = "" Then
            SelectSQL = "*"
        End If


        sql = "Select " & SelectSQL & " From " & FromSQL

        If TheRid = "" Then
            sql = sql & " where APF.Lead = 1 "
        Else
            sql = sql & " where APF.Lead = 1 and APF.RID = '" & TheRid & "'"
        End If


        'look for any LA_Summary fields



        ReportSQL = sql

    End Function

    Private Sub ToolStripButton6_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim tableview As DataView

        tableview = LoadDataView("select '' as A, '' as B, '' as C where 1 = 2")

        Dim rowView As DataRowView = tableview.AddNew

        ' Change values in the DataRow.
        rowView("A") = "Testing 123"
        rowView("B") = "This is a test"
        rowView("C") = "Please work"

        rowView.EndEdit()
        Dim RTFText As String

        RTFText = RTF_BuildTable(tableview)

        RichTextBoxEx1.SelectedRtf = RTFText


    End Sub

    Private Sub ToolStripButton8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton8.Click
        'new report.  clear everthing and start a new
        NewReport()

    End Sub

    Private Sub LoadReport(ByVal Report_ID As String)
        Dim sql As String

        sql = "Select * from RD_Reports" & _
              " where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Report_ID = '" & Report_ID & "'"

        Dim AView As DataView

        AView = LoadDataView(sql)

        If AView.Count = 0 Then Exit Sub

        PrintDocument1.DefaultPageSettings.Landscape = AView(0).Item("Alignment")
        PrintDocument1.DefaultPageSettings.Margins.Left = AView(0).Item("Left_M")
        PrintDocument1.DefaultPageSettings.Margins.Right = AView(0).Item("Right_M")
        PrintDocument1.DefaultPageSettings.Margins.Top = AView(0).Item("Top_M")
        PrintDocument1.DefaultPageSettings.Margins.Bottom = AView(0).Item("Bottom_M")
        TheReportName = AView(0).Item("Report_Name")
        Me.Text = TheReportName


        WYSIWYG_RTF(Me.RichTextBoxEx1, Me.PrintDocument1)
        Me.RichTextBoxEx1.Refresh()

        RichTextBoxEx1.Rtf = AView(0).Item("RTF")


    End Sub

    Private Sub SaveReport()
        Dim sql As String

        Dim Alignment As Integer = PrintDocument1.DefaultPageSettings.Landscape
        Dim LeftM As Integer = PrintDocument1.DefaultPageSettings.Margins.Left
        Dim RightM As Integer = PrintDocument1.DefaultPageSettings.Margins.Right
        Dim TopM As Integer = PrintDocument1.DefaultPageSettings.Margins.Top
        Dim BottomM As Integer = PrintDocument1.DefaultPageSettings.Margins.Bottom
        Dim scen_ID As String = ModelForm.ScenarioBox.SelectedValue.ToString



        If ReportID = "" Then
            'this report does not exist yet, so add the record


            Dim ReportName As String = InputBox("Please enter a name for the report", "Report Name")

            If ReportName = "" Then Exit Sub

            TheReportName = ReportName
            Me.Text = TheReportName
            ShowLoadingForm()




            ReportID = Format(Now, "ddMMyyhhmmssff")

            sql = "insert into RD_Reports (Scenario_ID, User_Name, Report_ID, Report_Name, RTF, Left_M, Right_M, Top_M, Bottom_M, Alignment) " & _
                  "values (@Scenario_ID, @User_Name, @Report_ID, @Report_Name, @RTF, @Left_M, @Right_M, @Top_M, @Bottom_M, @Alignment)"


            Dim cnn As SqlConnection
            cnn = New SqlConnection(StoreConnect)
            cnn.Open()
            Dim myCommand As New SqlCommand(sql, cnn)

            With myCommand.Parameters
                .AddWithValue("@Scenario_ID", scen_ID)
                .AddWithValue("@User_Name", CurrentUser.Name)
                .AddWithValue("@Report_ID", ReportID)
                .AddWithValue("@Report_Name", ReportName)
                .AddWithValue("@RTF", RichTextBoxEx1.Rtf)
                .AddWithValue("@Left_M", LeftM)
                .AddWithValue("@Right_M", RightM)
                .AddWithValue("@Top_M", TopM)
                .AddWithValue("@Bottom_M", BottomM)
                .AddWithValue("@Alignment", Alignment)
            End With

            myCommand.ExecuteNonQuery()

            cnn.Close()

            HideLoadingForm()


        Else
            'update the report
            ShowLoadingForm()

            sql = "update RD_Reports set RTF = @RTF, Left_M = @Left_M, Right_M = @Right_M, Top_M = @Top_M, Bottom_M = @Bottom_M, Alignment = @Alignment " & _
                  "where Scenario_ID = @Scenario_ID and Report_ID = @Report_ID"


            Dim cnn As SqlConnection
            cnn = New SqlConnection(StoreConnect)
            cnn.Open()
            Dim myCommand As New SqlCommand(sql, cnn)

            With myCommand.Parameters
                .AddWithValue("@Scenario_ID", scen_ID)
                '   .AddWithValue("@User_Name", CurrentUser)
                .AddWithValue("@Report_ID", ReportID)
                .AddWithValue("@RTF", RichTextBoxEx1.Rtf)
                .AddWithValue("@Left_M", LeftM)
                .AddWithValue("@Right_M", RightM)
                .AddWithValue("@Top_M", TopM)
                .AddWithValue("@Bottom_M", BottomM)
                .AddWithValue("@Alignment", Alignment)
            End With

            myCommand.ExecuteNonQuery()

            cnn.Close()
            HideLoadingForm()

        End If


    End Sub

    Private Sub RichTextBoxEx1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBoxEx1.TextChanged
        ChangesMade = True

    End Sub

    Private Sub SaveMe()
        SaveReport()
        SaveFields()

    End Sub

    Function FindProductFolder(ByVal Product As String) As String
        Dim sql As String
        Dim TheView As DataView
        Dim TheFolder As String = ""

        sql = "select * from Modelling_Products where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Product_Number = " & Product

        TheView = LoadDataView(sql)
        If TheView.Count = 0 Then
            TheFolder = ""
        Else
            If IsDBNull(TheView(0).Item("Folder_ID")) Then
                TheFolder = ""
            Else
                TheFolder = TheView(0).Item("Folder_ID")
            End If
        End If

        FindProductFolder = TheFolder

    End Function

    Private Sub LoadFieldsView()
        Dim Sql As String
        Dim ParentNode As TreeNode
        Dim TheNode As TreeNode

        Sql = "select Table_Name, Field_Name, Field_Code, Value, Parent, Parent_Parent, Node, Decimals, Zero_Text from RD_Report_Fields where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Report_ID ='" & ReportID & "'"

        AllFieldsView = LoadDataView(Sql)

        Dim r As DataRowView
        FieldCodes.Clear()
        For Each r In AllFieldsView
            FieldCodes.Add(r.Item("Field_Code"))
            'also add this now to the view
            With FieldsView

                If r.Item("Parent_Parent") = "" Then
                    'if it is a product make sure it is not in a folder
                    If r.Item("Parent") = "Modelling_Steps_Products" Or Mid(r.Item("Parent"), 1, 2) = "NF" Then
                        Dim TheFolder As String
                        TheFolder = FindProductFolder(Trim(r.Item("Node")))
                        ParentNode = .Nodes.Item("Modelling_Steps_Products")
                        If TheFolder = "" Then

                            TheNode = ParentNode.Nodes(r.Item("Node"))
                        Else

                            ParentNode = ParentNode.Nodes(TheFolder)
                            TheNode = ParentNode.Nodes(r.Item("Node"))
                        End If
                    Else
                        ParentNode = .Nodes.Item(r.Item("Parent"))
                        TheNode = ParentNode.Nodes(r.Item("Node"))
                    End If
                Else
                    ParentNode = .Nodes.Item(r.Item("Parent_Parent"))
                    ParentNode = ParentNode.Nodes.Item(r.Item("Parent"))
                    TheNode = ParentNode.Nodes(r.Item("Node"))
                End If

                If Not TheNode Is Nothing Then
                    TheNode.Text = "[" & r.Item("Field_Code") & "] " & TheNode.Text
                    TheNode.SelectedImageIndex = 3
                    TheNode.ImageIndex = 3
                End If

                
            End With
        Next

    End Sub

    Private Sub SaveFields()

        If ReportID = "" Then Exit Sub

        Dim r As DataRowView
        Dim sql As String

        sql = "delete from RD_Report_Fields where Scenario_ID = '" & ModelForm.ScenarioBox.SelectedValue.ToString & "' and Report_ID ='" & ReportID & "'"
        RunNonQuery(sql)

        For Each r In AllFieldsView
            sql = "insert into RD_Report_Fields (Scenario_ID, User_Name, Report_ID, Field_Code, Table_Name, Field_name, Value, Parent, Parent_Parent, Node, Decimals, Zero_Text) " & _
                " values('" & ModelForm.ScenarioBox.SelectedValue.ToString & "','" & CurrentUser.Name & "','" & ReportID & "','" & r.Item("Field_Code") & "','" & r.Item("Table_Name") & "','" & r.Item("Field_Name") & "','" & r.Item("Value") & "'," & _
                "'" & r.Item("Parent") & "','" & r.Item("Parent_Parent") & "','" & r.Item("Node") & "'," & r.Item("Decimals") & ",'" & r.Item("Zero_Text") & "')"
            RunNonQuery(sql)
        Next

    End Sub

    Private Sub PublishDTFields()

        If ReportID = "" Then Exit Sub

        Dim r As DataRowView
        Dim sql As String

        sql = "delete from RD_Published_DataTable_Fields where Report_ID ='" & ReportID & "'"
        RunNonQuery(sql)

        For Each r In DataTableFields
            sql = "insert into RD_Published_DataTable_Fields (Report_ID, Field_Code, Order_Num, Header) Values ('" & ReportID & "'," & _
                  "'" & r.Item("Field_Code") & "', " & r.Item("Order_Num") & ", '" & r.Item("Header") & "')"
            RunNonQuery(sql)
        Next

    End Sub


    Private Sub ToolStripButton9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton9.Click
        If LoadReportDialog.ShowDialog = Windows.Forms.DialogResult.OK Then

            With LoadReportDialog
                If .ReportsBox.Items.Count = 0 Then Exit Sub
                If .ReportsBox.SelectedIndex = -1 Then Exit Sub


                ShowLoadingForm()
                NewReport()
                Job("Loading Report, please wait...")
                LoadReport(.ReportsBox.SelectedValue)
                ReportID = .ReportsBox.SelectedValue
                LoadFieldsView()

                'check if report is published
                Dim sql As String
                sql = "Select Report_ID from RD_Published_Reports where Report_ID = '" & ReportID & "'"

                Dim repview As DataView
                repview = LoadDataView(sql)

                If repview.Count = 0 Then
                    RemovePubBut.Visible = False
                Else
                    RemovePubBut.Visible = True

                End If

                HideLoadingForm()
                Job("Ready")


            End With

        End If
        RichTextBoxEx1.Focus()
    End Sub

    Private Sub ReportDesignerForm_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        RichTextBoxEx1.Focus()
    End Sub

    'Private Sub PreviewFirst10RecordsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    PreviewReport(10)
    'End Sub

    'Private Sub PreviewAllRecordsmightTakeLongToLoadToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    PreviewReport(0)
    'End Sub

    'Private Sub ToolStripSplitButton1_ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    PreviewReport(10)
    'End Sub

    Private Sub PreviewReport(ByVal RecordCount As Integer, ByVal Period_ID As Integer)
        Me.RichTextBoxEx1.Refresh()

        ShowLoadingForm()
        Job("Creating report...")

        TempBox = RichTextBoxEx1.Rtf

        ReplaceValues(RecordCount, Period_ID)

        HideLoadingForm()
        Job("Ready")


        With PrintPreviewForm
            .NumberofPages = 0
            .PrintPreviewControl1.Refresh()
            .PrintPreviewControl1.Document = PrintDocument1
            .NumberofRecords = MainReportView.Count
            .CurrentRecord = 1
            .RecordLabel.Text = "Record 1 of " & MainReportView.Count
            .ShowDialog()
        End With

        PrintPreviewForm.Close()
        RichTextBoxEx1.Rtf = TempBox
        RichTextBoxEx1.Refresh()
    End Sub

    Public Function sRTF_To_HTML(ByVal sRTF As String) As String
        'Declare a Word Application Object and a Word WdSaveOptions object
        Dim MyWord As Microsoft.Office.Interop.Word.Application
        Dim oDoNotSaveChanges As Object = _
             Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges
        'Declare two strings to handle the data
        Dim sReturnString As String = ""
        Dim sConvertedString As String = ""
        Try
            'Instantiate the Word application, set visible to false and create a document
            MyWord = CreateObject("Word.application")
            MyWord.Visible = False
            MyWord.Documents.Add()
            'Create a DataObject to hold the Rich Text
            'and copy it to the clipboard
            Dim doRTF As New System.Windows.Forms.DataObject
            doRTF.SetData("Rich Text Format", sRTF)
            Clipboard.SetDataObject(doRTF)
            'Paste the contents of the clipboard to the empty,
            'hidden Word Document
            MyWord.Windows(1).Selection.Paste()
            'â€¦then, select the entire contents of the document
            'and copy back to the clipboard
            MyWord.Windows(1).Selection.WholeStory()
            MyWord.Windows(1).Selection.Copy()
            'Now retrieve the HTML property of the DataObject
            'stored on the clipboard
            sConvertedString = _
                 Clipboard.GetData(System.Windows.Forms.DataFormats.Html)
            'Remove some leading text that shows up in some instances
            '(like when you insert it into an email in Outlook
            sConvertedString = _
                 sConvertedString.Substring(sConvertedString.IndexOf("<html"))
            'Also remove multiple Ã‚ characters that somehow end up in there
            sConvertedString = sConvertedString.Replace("Ã,", "")
            sConvertedString = sConvertedString.Replace("Ã", "")
            sConvertedString = sConvertedString.Replace("Â", "")
            'and you're done.
            sReturnString = sConvertedString
            If Not MyWord Is Nothing Then
                MyWord.Quit(oDoNotSaveChanges)
                MyWord = Nothing
            End If
        Catch ex As Exception
            If Not MyWord Is Nothing Then
                MyWord.Quit(oDoNotSaveChanges)
                MyWord = Nothing
            End If
            MsgBox("Error converting Rich Text to HTML")
        End Try
        Return sReturnString
    End Function

    Public Function ConvertHTMLFields(ByVal html As String, ByVal RecordNumber As Integer) As String
        Dim DP As Integer
        Dim RTF As String
        Dim ZeroText As String
        Dim TheValue As String

        ReportBrowserForm.LastRecord = MainReportView.Count - 1

        If RecordNumber > MainReportView.Count - 1 Then
            ConvertHTMLFields = html
            Exit Function
        End If

        RTF = RichTextBoxEx1.Rtf
        RichTextBoxEx1.Text = html

        For Each r In AllFieldsView
            While RichTextBoxEx1.Find("[" & r.Item("Field_Code") & "]") <> -1
                If IsDBNull(MainReportView(0).Item(r.Item("Field_Code"))) Then
                    TheValue = "0"
                Else
                    TheValue = MainReportView(0).Item(r.Item("Field_Code")).ToString
                End If

                If TheValue = "0" Then
                    ZeroText = GetZeroText(r.item("Field_Code"))
                    TheValue = ZeroText
                End If

                DP = GetDecimals(r.Item("Field_Code"))
                If DP = -1 Then
                    RichTextBoxEx1.SelectedText = TheValue
                Else
                    Try
                        RichTextBoxEx1.SelectedText = FormatNumber(TheValue, DP, TriState.True, TriState.False, TriState.True)
                    Catch ex As Exception
                        RichTextBoxEx1.SelectedText = TheValue
                    End Try
                End If

            End While
        Next
        ConvertHTMLFields = RichTextBoxEx1.Text
        RichTextBoxEx1.Rtf = RTF
    End Function

    Public Function ConvertFields(ByVal Report As String) As String
        Dim DP As Integer
        Dim ZeroText As String = "0"
        Dim RTF As String
        Dim TheValue As String

        RTF = RichTextBoxEx1.Rtf
        RichTextBoxEx1.Rtf = Report

        If MainReportView.Count = 0 Then
            ConvertFields = ""
            Exit Function
        End If

        For Each r In AllFieldsView
            While RichTextBoxEx1.Find("[" & r.Item("Field_Code") & "]") <> -1


                ZeroText = GetZeroText(r.item("Field_Code"))


                If IsDBNull(MainReportView(0).Item(r.Item("Field_Code"))) Then
                    TheValue = "0"
                Else
                    TheValue = MainReportView(0).Item(r.Item("Field_Code")).ToString
                End If

                Try
                    If TheValue = 0 Then
                        'ZeroText = GetZeroText(r.item("Field_Code"))
                        TheValue = ZeroText
                    End If
                Catch ex As Exception
                    'keep the value as it is
                End Try


                DP = GetDecimals(r.Item("Field_Code"))
                If DP = -1 Then
                    RichTextBoxEx1.SelectedText = TheValue
                Else
                    Try
                        RichTextBoxEx1.SelectedText = FormatNumber(TheValue, DP, TriState.True, TriState.False, TriState.True)
                    Catch ex As Exception
                        RichTextBoxEx1.SelectedText = TheValue
                    End Try
                End If

            End While
        Next
        ConvertFields = RichTextBoxEx1.Rtf
        RichTextBoxEx1.Rtf = RTF
    End Function

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Dim html As String
        Dim DTRTF As String = ""
        Dim MainRTF As String


        Me.RichTextBoxEx1.Refresh()

        'Load the SQL
        Dim sql As String
        Dim ARid As String = ""

        With Search_Form
            .WhoSentForMe = "RepDesigner"
            .Text = "Search and Select a provider you wish to preview"
            If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                'find the provider in the list
                With .SearchResults_DataViewGrid

                    ARid = .Item(.Columns("RID").Index, .CurrentRow.Index).Value.ToString

                End With
            Else
                Exit Sub
            End If
        End With

        ShowLoadingForm()
        Job("Creating report...")

        'clear temp data
        ProcessCancelled = False
        RunNonQuery("delete from dbo.Modelling_Products_Data where User_Name = '" & CurrentUser.Name & "'")

        With ReportDesignerFilterForm
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                sql = ReportSQL(ARid, .PeriodBox.SelectedValue)
            Else
                Exit Sub
            End If
        End With



        If ProcessCancelled Then

            Try
                Modelling_Process.Close()

            Catch ex As Exception

            End Try

            HideLoadingForm()

            Exit Sub

        End If

        MainReportView = LoadDataView(sql)

        'create the datatable definitions
        CreateDTDef()

        If DataTableFields.Count <> 0 Then
            Dim dtsql As String = ""
            Dim c As Integer
            Dim TheHeader(DataTableFields.Count - 1) As String

            For c = 0 To DataTableFields.Count - 1
                TheHeader(c) = DataTableFields(c).Item("Header").ToString
                If Trim(DataTableFields(c).Item("Field_Code").ToString) <> "" Then
                    If dtsql = "" Then
                        dtsql = DataTableFields(c).Item("Field_Code").ToString
                    Else
                        dtsql = dtsql & ", " & DataTableFields(c).Item("Field_Code").ToString
                    End If
                End If

            Next

            dtsql = "Select " & dtsql & " from (" & sql & ") DataTableSQL"

            DTRTF = RTFDataTable(dtsql, TheHeader)
        End If


        ReportHTML = ""
        MainRTF = RichTextBoxEx1.Rtf

        If DTRTF <> "" Then
            Dim StartPos As Integer
            Dim EndPos As Integer

            StartPos = RichTextBoxEx1.Find("{DTS}")
            EndPos = RichTextBoxEx1.Find("{DTE}")

            If StartPos <> -1 And EndPos <> -1 Then
                RichTextBoxEx1.SelectionStart = StartPos
                RichTextBoxEx1.SelectionLength = EndPos - StartPos + 5

                RichTextBoxEx1.SelectedRtf = DTRTF
            End If
        End If

        With ReportBrowserForm
            .Text = TheReportName
            .MdiParent = MainForm
            .Dock = DockStyle.Fill
            .PreviousBut.Visible = False
            .NextBut.Visible = False
            .RecordSelector.Visible = False

            Dim TheReport As String
            TheReport = ConvertFields(RichTextBoxEx1.Rtf)

            TheReport = CalculateExpresions(TheReport, False)

            'html = sRTF_To_HTML(TheReport)
            'ReportHTML = html

            '' html = ConvertHTMLFields(html, 0)
            ''Remove some odd characters at the end of the document for some reason and also a strange looking A
            'html = html.Substring(0, html.IndexOf("</html>") + 8)
            'html = html.Replace("Ã,", "")
            'html = html.Replace("Ã", "")

            .CurrentRecord = 0
            '.Browser.DocumentText = html
            .RTBox.Rtf = TheReport
            .Show()
        End With


        RichTextBoxEx1.Rtf = MainRTF

        'RichTextBoxEx1.Rtf = TempBox
        RichTextBoxEx1.Refresh()

        HideLoadingForm()
        Job("Ready")

        Try
            Modelling_Process.Close()

        Catch ex As Exception

        End Try
    End Sub

    Private Sub PublishReport()
        '   Dim html As String
        Dim RTF As String
        Dim Thesql As String
        Dim Thewhere As String
        Dim dtsql As String
        Dim dtview As DataView
        Dim TheCol As DataColumn

        Job("Publishing report, please wait...")

        'do the filter
        HideLoadingForm()
        If ReportDesignerFilterForm.ShowDialog = Windows.Forms.DialogResult.OK Then


            Dim e As DataRowView
            Dim s As String = ""

            For Each e In ReportDesignerFilterForm.FilterListBox.SelectedItems

                If s = "" Then
                    s = "'" & e.Item(0).ToString() & "'"
                Else
                    s = s & ",'" & e.Item(0).ToString() & "'"
                End If

            Next

            '    MessageBox.Show(s)


            'If ReportDesignerFilterForm.FilterBox.SelectedValue = "<All>" Then
            '    Thewhere = " and APF.UPIN in (Select distinct UPIN from Provider_Funding_Streams where Period_ID = " & ReportDesignerFilterForm.PeriodBox.SelectedValue & ")"
            'Else
            Thewhere = " and APF.UPIN in (Select UPIN from Provider_Funding_Streams where Period_ID = " & ReportDesignerFilterForm.PeriodBox.SelectedValue & " and Budget_ID in (" & s & "))"
            'End If
        Else
            Exit Sub
        End If
        Me.Refresh()

        Job("Please wait...")

        ShowLoadingForm()

        '    html = sRTF_To_HTML(RichTextBoxEx1.Rtf)

        'Remove some odd characters at the end of the document for some reason and also a strange looking A
        '    html = html.Substring(0, html.IndexOf("</html>") + 8)
        '    html = html.Replace("Ã,", "")
        '    html = html.Replace("Ã", "")

        'clear temp data
        ProcessCancelled = False
        RunNonQuery("delete from dbo.Modelling_Products_Data where User_Name = '" & CurrentUser.Name & "'")

        Thesql = ReportSQL("", ReportDesignerFilterForm.PeriodBox.SelectedValue)

        If ProcessCancelled Then

            Try
                Modelling_Process.Close()

            Catch ex As Exception

            End Try

            HideLoadingForm()

            Exit Sub

        End If


        Thesql = Thesql & Thewhere

        CreateTableAndData(Thesql, ReportID)

        'now just create a new sql statement... easy!..oh but wait... we have data tables now!!! damn it!
        'create the datatable definitions
        CreateDTDef()
        If DataTableFields.Count <> 0 Then
            PublishDTFields()
            'new sql statemnet

            dtsql = "select top 1 * from RD_" & Trim(ReportID)
            dtview = LoadDataView(dtsql)

            dtsql = ""
            For Each TheCol In dtview.Table.Columns
                If dtsql = "" Then
                    dtsql = "min(" & TheCol.ColumnName & ") as " & TheCol.ColumnName
                Else
                    dtsql = dtsql & ", min(" & TheCol.ColumnName & ") as " & TheCol.ColumnName
                End If

            Next

            Thesql = "select " & dtsql & " from RD_" & Trim(ReportID) & " group by LA_Code"
        Else
            Thesql = "select * from RD_" & Trim(ReportID)
        End If

        RTF = RichTextBoxEx1.Rtf

        Dim sql As String

        Dim Alignment As Integer = PrintDocument1.DefaultPageSettings.Landscape
        Dim LeftM As Integer = PrintDocument1.DefaultPageSettings.Margins.Left
        Dim RightM As Integer = PrintDocument1.DefaultPageSettings.Margins.Right
        Dim TopM As Integer = PrintDocument1.DefaultPageSettings.Margins.Top
        Dim BottomM As Integer = PrintDocument1.DefaultPageSettings.Margins.Bottom
        Dim scen_ID As String = ModelForm.ScenarioBox.SelectedValue.ToString

        'first delete any old reports
        sql = "delete from RD_Published_Reports where Report_ID = '" & ReportID & "'"
        RunNonQuery(sql)


        sql = "insert into RD_Published_Reports (Report_ID, Report_Name, RTF, HTML, SQL, Left_M, Right_M, Top_M, Bottom_M, Alignment) " & _
              "values (@Report_ID, @Report_Name, @RTF, @HTML, @SQL, @Left_M, @Right_M, @Top_M, @Bottom_M, @Alignment)"


        Dim cnn As SqlConnection
        cnn = New SqlConnection(StoreConnect)
        cnn.Open()
        Dim myCommand As New SqlCommand(sql, cnn)

        With myCommand.Parameters
            .AddWithValue("@Report_ID", ReportID)
            .AddWithValue("@Report_Name", TheReportName)
            .AddWithValue("@RTF", RTF)
            .AddWithValue("@HTML", "")
            .AddWithValue("@SQL", Thesql)
            .AddWithValue("@Left_M", LeftM)
            .AddWithValue("@Right_M", RightM)
            .AddWithValue("@Top_M", TopM)
            .AddWithValue("@Bottom_M", BottomM)
            .AddWithValue("@Alignment", Alignment)
        End With

        myCommand.ExecuteNonQuery()

        cnn.Close()



        sql = "delete from RD_Published_Report_Fields where Report_ID ='" & ReportID & "'"
        RunNonQuery(sql)

        For Each r In AllFieldsView
            sql = "insert into RD_Published_Report_Fields (Report_ID, Field_Code, Decimals, Zero_Text) " & _
                " values('" & ReportID & "','" & r.Item("Field_Code") & "'," & r.Item("Decimals") & ",'" & r.item("Zero_Text") & "')"
            RunNonQuery(sql)
        Next

        MessageBox.Show("The report has been published", "Published!", MessageBoxButtons.OK, MessageBoxIcon.Information)


    End Sub

    Private Sub ToolStripButton10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton10.Click


        Job("Publishing report, please wait (this might take a while)...")
        ShowLoadingForm()

        SaveReport()
        SaveFields()

        If ReportID = "" Then
            HideLoadingForm()
            MessageBox.Show("The report was not published.", "Cancelled by user", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Job("Ready")
            Exit Sub
        End If
        ShowLoadingForm()
        PublishReport()

        RemovePubBut.Visible = True


        HideLoadingForm()
        Job("Ready")
    End Sub


    Private Sub CreateTableAndData(ByVal sql As String, ByVal TheReportID As String)
        Dim s As String
        Dim TheTableName As String
        Dim EM As String

        Job("Loading Data, please wait...")

        TheTableName = "RD_" & Trim(TheReportID)

        'delete the table if it exists
        Try
            s = "drop table " & TheTableName
            RunNonQuery(s)
        Catch ex As Exception
            'the table does not exist
        End Try

        s = "select * into " & TheTableName & " from (" & sql & ") TheQ"

        EM = RunNonQuery(s)


        If EM <> "" Then
            MessageBox.Show("There was an error.  Call Clifford! - " & EM, "Call Clifford!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If



    End Sub

    Private Sub RemovePubBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemovePubBut.Click

    End Sub


    Private Sub SettingsMenu_Opening(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles SettingsMenu.Opening
        'should the menu open?



        Try
            If IsNothing(FieldsView.SelectedNode.Parent) Then
                e.Cancel = True
                Exit Sub
            End If
        Catch ex As Exception
            e.Cancel = True
            Exit Sub
        End Try


        'does the selected node have children?
        If FieldsView.SelectedNode.Nodes.Count > 0 Then
            e.Cancel = True
            Exit Sub
        End If


        Dim TheCode As String

        If FieldsView.SelectedNode.Text.Contains("[") Then
            ' TheCode = Mid(FieldsView.SelectedNode.Text, 2, 3)
            e.Cancel = False

        Else
            e.Cancel = True
        End If



    End Sub

    Private Sub DecimalsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DecimalsToolStripMenuItem.Click
        Dim Decimals As Integer
        Dim TheResp As String
        Dim ACode As String
        Dim OldDeci As Integer

        If FieldsView.SelectedNode.Text.Contains("[") Then
            ACode = Mid(FieldsView.SelectedNode.Text, 2, 3)
        Else
            Exit Sub
        End If

        OldDeci = FindDecimals(ACode)

        If OldDeci = -1 Then
            MessageBox.Show("You cannot edit the decimal places for this field", "No decimals", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        TheResp = InputBox("Please enter the number of decimals", "Decimals", OldDeci.ToString)

        If TheResp <> "" Then
            Try
                Decimals = TheResp
            Catch ex As Exception
                MessageBox.Show("You must enter a number", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
            EditDecimals(ACode, Decimals)

        End If

    End Sub

    Private Sub ToolStripSplitButton2_ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripSplitButton2.ButtonClick
        Job("Saving Report, please wait...")

        SaveMe()

        Job("Ready")
    End Sub

    Private Sub SaveReportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveReportToolStripMenuItem.Click
        Job("Saving Report, please wait...")

        SaveMe()

        Job("Ready")
    End Sub

    Private Sub SaveReportAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveReportAsToolStripMenuItem.Click
        'save the old ID and set the ReportID to blank
        Dim OldID As String

        OldID = ReportID

        ReportID = ""

        Job("Saving Report, please wait...")

        SaveMe()

        Job("Ready")

        'check to see if it was actually saved
        If ReportID = "" Then
            ReportID = OldID
        Else
            RemovePubBut.Visible = False
        End If



    End Sub

    Private Sub ToolStripButton6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton6.Click
        If ReportID = "" Then
            MessageBox.Show("You have not saved the report yet.  Please save the report first.", "Save report first.", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        Dim ReportName As String = InputBox("Please enter a name for the report", "Report Name")

        If ReportName = "" Then Exit Sub

        TheReportName = ReportName
        Dim sql As String

        sql = "update RD_Reports set Report_Name = '" & TheReportName & "' where Report_ID = '" & ReportID & "'"
        RunNonQuery(sql)
        Me.Text = TheReportName

    End Sub

    Public Sub AddRow(ByVal sb As RTFBuilderbase, ByVal LString As String, ByVal cellContents() As String)
        Dim p As Padding = New Padding()
        'Padding is ignored

        'Create row definition
        Dim rowwidth As Integer

        rowwidth = LineWidth / 130

        Dim i As Integer = 0
        Dim TotalWidth As Integer = 0
        Dim TN As String
        Dim cw(cellContents.Length) As Integer

        Do While (i < cellContents.Length)
            TN = LString(i)
            Try
                If TN = "1" Then
                    TotalWidth = TotalWidth + 100
                    cw(i) = 100
                Else
                    TotalWidth = TotalWidth + 30
                    cw(i) = 30
                End If

            Catch ex As Exception
                TotalWidth = TotalWidth + 100
                cw(i) = 100

            End Try
            i = (i + 1)
        Loop


        Dim rd As RTFRowDefinition = New RTFRowDefinition(rowwidth, RTFAlignment.MiddleCenter, RTFBorderSide.Default, 15, SystemColors.WindowText, p)

        'Create Cell Definitions
        Dim cds(cellContents.Length) As RTFCellDefinition
        i = 0
        Do While (i < cellContents.Length)
            If LString(i) = "1" Then
                cds(i) = New RTFCellDefinition((rowwidth * (cw(i) / TotalWidth)), RTFAlignment.MiddleLeft, RTFBorderSide.Default, 15, Color.Black, Padding.Empty)
            Else
                cds(i) = New RTFCellDefinition((rowwidth * (cw(i) / TotalWidth)), RTFAlignment.MiddleRight, RTFBorderSide.Default, 15, Color.Black, Padding.Empty)
            End If

            i = (i + 1)
        Loop
        Dim pos As Integer = 0
        ' Enumerate the cells
        ' Each cell exposes an RtfBuilderbase so you can insert images,
        ' format rtf i.e. all RtfBuilder functions , within the cell
        For Each item As RTFBuilderbase In sb.EnumerateCells(rd, cds)
            If pos < cellContents.Length Then
                item.BackColor(Color.White)
                item.Append(cellContents(pos))
            End If
            pos = pos + 1
        Next
    End Sub


    Public Function RTFDataTable(ByVal TheSQL As String, ByVal HeadersRow() As String) As String

        Dim rtf As String = ""
        Dim d As Integer

        'lets try something crazy here!

        Dim aview As DataView
        Dim sql As String

        sql = TheSQL
        aview = LoadDataView(sql)

        If aview.Count = 0 Then
            Return ""
            Exit Function
        End If

        Dim acol As DataColumn
        Dim r As Integer
        Dim colcount As Integer = 0
        Dim i As Integer = -1

        'run through the first record just to check if it is a number or a string
        Dim LString As String = ""

        For Each acol In aview.Table.Columns
            If acol.DataType.ToString = "System.String" Then
                LString = LString & "1"
            Else
                LString = LString & "2"
            End If
        Next


        colcount = aview.Table.Columns.Count

        Dim mystring(colcount - 1) As String

        'do the header first
        Dim sb As New RTFBuilder
        AddRow(sb, LString, HeadersRow)

        For r = 0 To aview.Count - 1
            i = -1
            For Each acol In aview.Table.Columns
                i = i + 1
                'sort out the decimals
                d = GetDecimals(acol.ColumnName.ToString)

                If IsDBNull(aview(r).Item(acol.ColumnName.ToString)) Then
                    mystring(i) = ""
                Else
                    If d <> -1 Then
                        mystring(i) = FormatNumber(aview(r).Item(acol.ColumnName.ToString), d, TriState.True, TriState.False, TriState.True)
                    Else
                        mystring(i) = aview(r).Item(acol.ColumnName.ToString)
                    End If
                End If



            Next
            AddRow(sb, LString, mystring)

        Next
        rtf = sb.ToString

        Return rtf
    End Function

    Private Sub ToolStripButton11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton11.Click
        Dim ColumnCountS As String
        Dim Colcount As Integer


        ColumnCountS = InputBox("How many columns should the data table have?", "Number of Columns", "")

        If ColumnCountS = "" Then Exit Sub

        Try
            Colcount = ColumnCountS
        Catch ex As Exception
            MessageBox.Show("You need to enter a number", "Not a number", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        Dim TheHeader(Colcount - 1) As String
        Dim c As Integer
        Dim LString As String = ""

        For c = 0 To Colcount - 1
            TheHeader(c) = "Header " & c.ToString
            LString = LString & "1"
        Next

        Dim sb As New RTFBuilder
        AddRow(sb, LString, TheHeader)

        For c = 0 To Colcount - 1
            TheHeader(c) = "Data " & c.ToString
        Next
        AddRow(sb, LString, TheHeader)

        RichTextBoxEx1.SelectedText = "{DTS}"
        RichTextBoxEx1.SelectionStart = RichTextBoxEx1.SelectionStart + 5
        RichTextBoxEx1.SelectedRtf = sb.ToString
        RichTextBoxEx1.SelectedText = "{DTE}"

        'RichTextBoxEx1.SelectedRtf = RTFDataTable("select top 5 * from modelling_other ", TheHeader)

    End Sub

    Private Sub CreateDTDef()

        Dim StartPos As Integer
        Dim EndPos As Integer

        StartPos = RichTextBoxEx1.Find("{DTS}")
        EndPos = RichTextBoxEx1.Find("{DTE}")

        'now save the header and fields to a table and you are done
        CreateDataTableFieldsView()

        If StartPos <> -1 And EndPos <> -1 Then


            RichTextBoxEx1.SelectionStart = StartPos
            RichTextBoxEx1.SelectionLength = EndPos - StartPos + 5
            Clipboard.SetDataObject(RichTextBoxEx1.SelectedText, TextDataFormat.Text)

            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet

            xlApp = New Excel.Application

            xlWorkBook = xlApp.Workbooks.Add
            xlWorkSheet = xlWorkBook.Worksheets.Add

            xlWorkSheet.Paste()
            '        xlApp.Visible = True

            'get the header
            'lets count to see how many columns I have

            'but first see where we should start
            Dim c As Integer
            Dim StartRow As Integer = 0

            For c = 1 To 10
                If Trim(xlWorkSheet.Cells(c, 1).value) <> "" Then
                    If xlWorkSheet.Cells(c, 1).value <> "{DTS}" Then
                        If xlWorkSheet.Cells(c, 1).value <> vbCrLf Then
                            If StartRow = 0 Then
                                StartRow = c
                            End If
                        End If
                    End If
                End If
            Next


            c = 1

            Dim ts As String


            ts = xlWorkSheet.Cells(StartRow, c).value

            If ts <> "" Then

                While ts <> ""
                    c = c + 1

                    ts = xlWorkSheet.Cells(StartRow, c).value

                End While
            Else
                c = -1
            End If

            'column count is...
            c = c - 1

            'create a string array for header and fields
            Dim HeaderArray(c) As String
            Dim FieldsArray(c) As String

            'save the values to the arrays

            Dim cc As Integer
            Dim cr As Integer

            For cr = 1 To 2
                For cc = 1 To c
                    If cr = 1 Then

                        HeaderArray(cc) = xlWorkSheet.Cells(StartRow, cc).value

                    Else
                        FieldsArray(cc) = xlWorkSheet.Cells(StartRow + 1, cc).value
                    End If
                Next

            Next

            ' xlApp.Visible = True

            If Not xlApp Is Nothing Then

                xlWorkBook.Close(False)
                xlApp.Quit()

                xlApp = Nothing
            End If



            ' Change values in the DataRow.
            For cc = 1 To c
                Dim rowView As DataRowView = DataTableFields.AddNew
                rowView("Report_ID") = ReportID
                rowView("Field_Code") = FieldsArray(cc)
                rowView("Order_Num") = cc
                rowView("Header") = HeaderArray(cc)
                rowView.EndEdit()
            Next



        End If


    End Sub


    Private Sub ArchiveReportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ArchiveReportToolStripMenuItem.Click
        'Archive the report

        Dim mes As String
        mes = "This action will archive the current published report.  This means that the current published report and data will be moved to an archived state that will detatch it from the report design and scenario.  Are you sure you want to continue?"

        If MessageBox.Show(mes, "Archive report?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim NewID As String
            NewID = Format(Now, "ddMMyyhhmmssff")

            Dim ArcName As String
            Dim ShortName As String

            ShortName = Mid(TheReportName, 1, 50 - Len(" (Archived)"))

            ArcName = InputBox("Please enter a name for the archived report", "Archive report", ShortName & " (Archived)")

            If ArcName = "" Then Exit Sub

            'RD_Published_Reports
            Dim sql As String

            'change the Sql
            Dim selsql As String
            Dim newsql As String
            Dim sqlview As DataView

            sqlview = LoadDataView("select sql from RD_Published_Reports where Report_ID = '" & ReportID & "'")
            selsql = sqlview(0).Item("sql").ToString

            newsql = selsql.Replace(Trim(ReportID.ToString), Trim(NewID.ToString)).ToString

            sql = "update RD_Published_Reports set Report_ID = '" & NewID & "', Report_Name = '" & ArcName & "', sql = '" & newsql & "' where Report_ID = '" & ReportID & "'"
            RunNonQuery(sql)

            'RD_Published_Report_Fields
            sql = "update RD_Published_Report_Fields set Report_ID = '" & NewID & "' where Report_ID = '" & ReportID & "'"
            RunNonQuery(sql)

            'RD_Published_DataTable_Fields
            sql = "update RD_Published_DataTable_Fields set Report_ID = '" & NewID & "' where Report_ID = '" & ReportID & "'"
            RunNonQuery(sql)

            'The Actual report data
            sql = "sp_rename RD_" & ReportID & ", RD_" & NewID
            RunNonQuery(sql)

            MessageBox.Show("The report has been archived", "Archived", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End If
    End Sub

    Private Sub RemovePubBut_ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemovePubBut.ButtonClick
        If MessageBox.Show("Are you sure you want to remove the publication?", "Remove Publication?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim sql As String
            Job("Removing publication...")
            ShowLoadingForm()
            Me.Refresh()
            sql = "delete from RD_Published_Reports where Report_ID = '" & ReportID & "'"
            RunNonQuery(sql)
            sql = "delete from RD_Published_Report_Fields where Report_ID = '" & ReportID & "'"
            RunNonQuery(sql)
            RemovePubBut.Visible = False
            HideLoadingForm()
        End If
        Job("Ready")
    End Sub


    Private Sub ToolStripButton12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton12.Click
        RichTextBoxEx1.SelectedText = Chr(12)

    End Sub

   
    Public Function CalculateExpresions(ByVal Report As String, ByVal HTML As Boolean) As String
        Dim RTF As String
        Dim a As Integer
        Dim TheString As String
        Dim TheCalc As String
        Dim TheAnswer As String = "#Error#"
        Dim LastCheck As Integer
        Dim startpoint As Integer
        Dim Answerconvert As Double

        RTF = RichTextBoxEx1.Rtf

        If HTML Then
            RichTextBoxEx1.Text = Report
        Else
            RichTextBoxEx1.Rtf = Report
        End If

        If HTML Then
            startpoint = RichTextBoxEx1.Find("<body")
        Else
            startpoint = 0
        End If

        'find each { and ending with }
        a = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
        LastCheck = a

        While a <> -1
            'find the end position }
            If RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase) <> -1 Then
                If a >= startpoint Then
                    TheString = Mid(RichTextBoxEx1.Text, 1, RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase))

                    TheString = Mid(TheString, a + 2, Len(TheString))

                    TheCalc = TheString

                    TheCalc = TheCalc.Replace("{", "")
                    TheCalc = TheCalc.Replace("}", "")

                    If TheCalc <> "DTS" Then
                        If TheCalc <> "DTE" Then


                            RichTextBoxEx1.SelectionStart = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
                            RichTextBoxEx1.SelectionLength = RichTextBoxEx1.Find("}", a, RichTextBoxFinds.MatchCase) - RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase) + 1

                            RichTextBoxEx1.Find("{" & TheString & "}")
                            Try
                                If TheCalc.ToUpper.Contains("WHEN") Then
                                    Dim thea As DataTable

                                    TheCalc = TheCalc.Replace("&lt;", "<")
                                    TheCalc = TheCalc.Replace("&gt;", ">")

                                    thea = LoadDataTable("select CASE " & TheCalc & " END as Value")
                                    TheAnswer = thea(0).Item("Value")
                                    'try converting it to a value first if not then it is text
                                    Try
                                        Answerconvert = TheAnswer
                                        RichTextBoxEx1.SelectedText = Format(Answerconvert, "#,0.###########")
                                    Catch ex As Exception
                                        RichTextBoxEx1.SelectedText = TheAnswer
                                    End Try
                                Else
                                    TheAnswer = EvaluateExpression(TheCalc)
                                    Answerconvert = TheAnswer
                                    RichTextBoxEx1.SelectedText = Format(Answerconvert, "#,0.###########")
                                End If

                            Catch ex As Exception
                                RichTextBoxEx1.SelectedText = "#Error#"
                            End Try



                        End If
                    End If

                End If
            End If

            a = RichTextBoxEx1.Find("{", startpoint, RichTextBoxFinds.MatchCase)
            If LastCheck = a Then
                a = -1
            Else
                LastCheck = a
            End If
        End While

        If HTML Then
            CalculateExpresions = RichTextBoxEx1.Text
        Else
            CalculateExpresions = RichTextBoxEx1.Rtf
        End If


        RichTextBoxEx1.Rtf = RTF
    End Function



    Private Sub ReplaceZerosWithToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReplaceZerosWithToolStripMenuItem.Click

        Dim TheResp As String
        Dim ACode As String
        Dim OldText As String
        Dim ZeroText As String

        If FieldsView.SelectedNode.Text.Contains("[") Then
            ACode = Mid(FieldsView.SelectedNode.Text, 2, 3)
        Else
            Exit Sub
        End If

        OldText = FindZeroText(ACode)

        If OldText = "-1" Then
            MessageBox.Show("You cannot edit the Zero Text for this field", "Not possible", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        ZeroText = OldText


        TheResp = InputBox("Please enter the text you would like to display instead of a Zero (Type BLANK if you would like to see a blank)", "Zero Text", ZeroText)

        If TheResp <> "" Then

            If TheResp.ToUpper = "BLANK" Then
                EditZeroText(ACode, "")
            Else
                EditZeroText(ACode, TheResp)
            End If


        End If
    End Sub


End Class

